﻿CREATE PROCEDURE [dbo].[spMomPortOrders] ( @OwnerID int )
	as
begin
	set nocount on
	declare @message nvarchar(1000)
	set @message = 'MOMPortOrders' + '  ' + convert( varchar, getdate(), 9 )
	RAISERROR (@message, 0, 1) WITH NOWAIT

	declare @orderCode char(1)
	set @orderCode = 'E'
	declare @alt_ordCode char(1)
	set @alt_ordCode = 'F'
	declare @custnumCode char(1)
	set @custnumCode = 'C'

	if object_id('tempdb..##cms') is null
	begin
		select * into ##cms from openquery( MOMPort, 
			'select order, custnum, shipnum,  
				odr_date, r_merch, shipping, tax, ord_total, tb_ship,
				order_st2, cl_key, quotation, alt_order
			from cms' )
		create index idx_tmpCms on ##cms ( [order] )
	end

	if object_id('tempdb..##items') is null
	begin
		select * into ##items from openquery( MOMPort,
			'select * from items' )
		create index idx_tmpItems on ##items ( [order] )
	end

	-- We are going to assume that a given customer will have only one instance of a given
	-- club membership at a given time.  This is necessary because there is no link in the
	-- database from the clubhist table to the clubsub table.  We therefore need to create
	-- an implicit link through the club_code and cust fields.

	--drop table ##clubsubs
	if object_id( 'tempdb..##clubsubs' ) is null
	begin
		select *, cast(null as int) as SequenceOrderID into ##clubsubs 
		from openquery( MOMPort, 
			'select T2.custnum, T1.club_code, T1.join_date, T1.next_ship, T1.num_ship, T1.num_sent, T1.status, T1.order
			from clubsubs T1
				join cms T2 on T1.order = T2.order' )
		create index idx_tmpClubSubs on ##clubsubs ( custnum, club_code )
		create index idx_tmpClubSubs2 on ##clubsubs ( SequenceOrderID )
		create index idx_tmpClubSubs3 on ##clubsubs( [order] )
	end

	if object_id( 'tempdb..##clubhist' ) is null
	begin
		select *, 
			cast( null as int ) as SequenceStepID,
			cast( null as int ) as SequenceOrderStepID
		into ##clubhist 
		from openquery( MOMPort, 
			'select T2.custnum, T1.order, T1.club_code, T1.nth_ship, T2.odr_date, T3.order as origorder
			from clubhist T1
				join cms T2 on T1.order = T2.order
				join items T3 on T1.origitemid = T3.item_id' )
		create index idx_tmpClubHist on ##clubhist ( [order], SequenceOrderStepID )
		create index idx_tmpClubHist2 on ##clubhist ( custnum, club_code )
		create index idx_tmpClubHist3 on ##clubhist ( SequenceStepID, custnum )
		create index idx_tmpClubHist4 on ##clubhist ( origorder )
	end


--	update T1 set StartOrder = ( select max([order])
--			from ##clubsubs 
--			where join_date <= T1.odr_date 
--				and custnum = T1.custnum
--				and club_code = T1.club_code) 
--	from ##clubhist T1

	-- Find a SequenceStep that matches to the clubhist 
	update T1 set SequenceStepID = coalesce( T2.SequenceStepID, T3.SequenceStepID )
	from ##clubhist T1
		left outer join ##tmpSequenceStep T2 on T1.club_code = T2.ClubCode
			and T1.nth_ship = T2.step 
		left outer join ##tmpSequenceStep T3 on T1.club_code = T3.ClubCode
			and T3.step = 0

	declare @orderNumber int
	declare @OrderID int
	declare @shipNum int
	declare @i int
	set @i = 0
	select [order], shipnum 
		into #foo
		from ##cms 
		where [order] not in ( select IDValue from tblAltCustomerID where IDSourceCode = @orderCode and OwnerID= @OwnerID )
		order by [order]
	declare cCms cursor local for select [order], shipnum from #foo
	open cCms
	fetch next from cCms into @orderNumber, @shipNum
	while @@fetch_status = 0
	begin
		if @i % 1000 = 0
		begin
			set @message = cast( @i as varchar ) + '  ' + convert( varchar, getdate(), 9 )
			RAISERROR (@message, 0, 1) WITH NOWAIT
		end
		set @i = @i + 1
		declare @CustomerID int
		declare @custnum int
		set @CustomerID = null
		select @custnum = custnum from ##cms where [order] = @orderNumber
		select @CustomerID = CustomerID
		from tblAltCustomerID with ( index ( IDX_AltCustomerIDByValue ) )
		where OwnerID = @OwnerID and IDSourceCode = @custnumCode and IDValue = cast(@custnum as varchar(20))

		if @CustomerID is not null 
		begin
			-- Get the ship-to name and address
			declare @shipTo_AddressID int
			declare @shipTo_PersonID int
			select @shipTo_AddressID = T2.AddressID, @shipTo_PersonID = T2.PersonID
			from tblCustomer T1
				join tblPerson T2 on T1.PersonID = T2.PersonID
			where T1.CustomerID = @CustomerID
			-- If the shipnum is not null then create new person and address records for the ship-to
			if coalesce(@shipNum, 0) <> 0
			begin
				insert into tblAddress (Address1, Address2, Address3, City, State, PostalCode, CountryID )
				select addr, addr2, null, city, state, zipcode, coalesce( T3.CountryID, 1 ) 
				from ##cust T1 
					left outer join ##country T2 on T1.country = T2.country
					left outer join codeCountry T3 on T2.name = T3.name
				where T1.custnum = @shipNum
				set @shipTo_AddressID = SCOPE_IDENTITY()

				insert into tblPerson ( FirstName, MiddleName, LastName, Name, LanguageID, AddressID )
				select FirstName, null, LastName, dbo.MakeName(FirstName, LastName), 1, @shipTo_AddressID
				from ##cust 
				where custnum = @shipNum
				set @shipTo_PersonID = SCOPE_IDENTITY()
			end

			-- Locate the sequence step for this order
			-- There can be multiple ClubHist records for a single order
			-- so this will pick just one
			declare @SequenceOrderStepID int
			set @SequenceOrderStepID = null
			select top 1 @SequenceOrderStepID = SequenceOrderStepID
			from ##clubhist
			where [order] = @orderNumber

			-- Create the OrderHeader record
			insert into tblOrderHeader ( CustomerID, 
				OrderDate, 
				ProjShipDate, 
				ProductTotal, 
				Shipping, 
				Taxes, 
				OrderTotal, 
				ImportDate, 
				HoldReasonCode, 
				ShipTo_PersonID, 
				ShipTo_CompanyName, 
				ShipTo_AddressID, 
				OrderOriginCode, 
				OrderSourceID,
				OrderStatusCode,
				CanceledReasonID,
				OwnerID,
				SequenceOrderStepID,
				SequenceCount
				)
			select @CustomerID, 
				dbo.SmallDTCast(T1.odr_date), 
				dbo.SmallDTCast(T1.odr_date), 
				T1.r_merch,
				T1.tb_ship,
				T1.tax,
				T1.ord_total,
				dbo.SmallDTCast(T1.odr_date), -- Import Date
				-- Hold Reason Code
				case order_st2 when 'CA' then 'Y'
					when 'CD' then 'Y'
					when 'CK' then 'Y'
					when 'CP' then 'Y'
					when 'EP' then 'A'
					when 'HD' then 'A'
					when 'HS' then 'A' 
					when 'II' then 'Y'
					when 'PE' then 'P'
					when 'UO' then 'A' 
					else null end,
				@shipTo_PersonID,
				T4.company,
				@shipTo_AddressID,
				'I',
				T5.OrderSourceID,
				-- Order Status codes come from statdesc
				case T1.order_st2 
					when 'BI' then 'P' 
					when 'BO' then 'B' 
					when 'CA' then 'H' 
					when 'CD' then 'H' 
					when 'CK' then 'H' 
					when 'CN' then 'C'
					when 'CP' then 'H' 
					when 'CS' then 'S'
					when 'EP' then 'H' 
					when 'GC' then 'P' 
					when 'HD' then 'H' 
					when 'HS' then 'H' 
					when 'II' then 'H' 
					when 'IN' then 'E' 
					when 'NW' then 'E' 
					when 'PA' then 'E'
					when 'PE' then 'H' 
					when 'PI' then 'E' 
					when 'PS' then 'E' 
					when 'QO' then 'P'
					when 'SC' then 'E' 
					when 'SH' then 'S' 
					when 'UO' then 'H' 
					end,
				-- Canceled Reason Code
				case when order_st2 = 'CN' then ( select CanceledReasonID from codeCanceledReason where Description = 'Canceled' and OwnerID is null )
					else null end,
				@OwnerID,
				T2.SequenceOrderStepID,
				T2.nth_ship
			from ##cms T1
				left outer join ( select max(nth_ship) as nth_ship, SequenceOrderStepID, [order]
					from ##clubhist 
					group by [order], SequenceOrderStepID ) as T2 on T2.SequenceOrderStepID = @SequenceOrderStepID
						and T2.[order] = @orderNumber
				join ##cust T4 on T1.custnum = T4.custnum
				left outer join tblOrderSource T5 on T1.cl_key = T5.[Description] and T5.OwnerID = @OwnerID
			where T1.[order] = @orderNumber

			set @OrderID = SCOPE_IDENTITY()

			-- Set the LastOrder reference in the SequenceOrderStep table.
			if @SequenceOrderStepID is not null
				update tblSequenceOrderStep set LastOrderID = @OrderID
				where SequenceOrderStepID = @SequenceOrderStepID

			insert into tblOrderDetail ( 
				OrderID, 
				ProductID, 
				Qty,
				UnitPrice,
				UnitShipping,
				Discount,
				PreferredCarrierCode,
				ImportDate )
			select @OrderID,
				T3.ProductID,
				T2.quanto,
				T2.it_unlist,
				0,
				cast(T2.discount as float) / 100,
				T4.CarrierCode,
				dbo.SmallDTCast(T1.odr_date)
			from ##cms T1
				join ##items T2 on T1.[order] = T2.[order]
				join tblProduct T3 on T2.item = T3.ItemCode
					and T3.ownerid = @OwnerID
				left outer join codeCarrier T4 on T2.ship_via = T4.abbrev
			where T1.[order] = @orderNumber
				and it_unlist >= 0

			-- ##tmpSequenceStep ( SequenceStepID int, ClubCode varchar(10), step int )
			-- If this was a trigger into a sequence then create or locate the SequenceOrder for this order
			if exists ( select 1 from ##clubsubs where [order] = @orderNumber and SequenceOrderID is null )
			begin
				declare @SequenceOrderID int
				declare @SequenceID int

				-- Get the Sequence based on the initial step from the ##tmpSequenceStep table
				-- Each club code is going to be linked to one sequence through ##tmpSequenceStep
				select top 1 @SequenceID = T3.SequenceID
				from ##clubsubs T1 
					join ##tmpSequenceStep T2 on T1.club_code = T2.ClubCode
					join tblSequenceStep T3 on T2.SequenceStepID = T3.SequenceStepID
				where T1.[order] = @orderNumber 

				-- Create the new SeuqenceStep records for the current order
				-- from the selected base sequence
				exec @SequenceOrderID = spCreateSequenceOrder @CustomerID, @OrderID, @SequenceID
				update tblSequenceOrderStep set NextTriggerDate = null where SequenceOrderID = @SequenceOrderID
				update T1 set CreateDate = dbo.SmallDTCast(T2.odr_date)
					from tblSequenceOrder T1,
						##cms T2
					where T1.SequenceOrderID = @SequenceOrderID
						and T2.[order] = @orderNumber

				-- Update the ##clubsubs table to point to the new SequenceOrder record
				update T1 set SequenceOrderID = @SequenceOrderID
				from ##clubsubs T1 
				where T1.[order] = @orderNumber
				--update T4 set SequenceOrderID = @SequenceOrderID
				--from ##clubsubs T1 
				--	join ##clubhist T2 on T1.club_code = T2.club_code and T1.custnum = T2.custnum
				--	join ##clubhist T3 on T2.SequenceStepID = T3.SequenceStepID and T1.custnum = T3.custnum
				--	join ##clubSubs T4 on T3.club_code = T4.club_code and T1.custnum = T4.custnum
				--where T1.[order] = @orderNumber

				-- Update the clubhist record to point to the new SequenceOrderStep records
				-- for all clubhist records matching the SequenceStep for this customer where the
				-- order date is after this trigger.
				update T1 set SequenceOrderStepID = T2.SequenceOrderStepID
				from ##clubhist T1
					join tblSequenceOrderStep T2 on T1.SequenceStepID = T2.SequenceStepID
				where T1.origorder = @orderNumber
					and T2.SequenceOrderID = @SequenceOrderID
				--update T1 set SequenceOrderStepID = T2.SequenceOrderStepID
				--from ##clubhist T1
				--	join tblSequenceOrderStep T2 on T1.SequenceStepID = T2.SequenceStepID
				--	join tblSequenceOrder T3 on T2.SequenceOrderID = T3.SequenceOrderID
				--	,##cms as T4
				--where T3.SequenceOrderID = @SequenceOrderID
				--	and T1.custnum = @custnum
				--	and T3.OrderID = @OrderID
				--	and T4.[order] = @orderNumber
				--	and T1.odr_date >= T4.odr_date
			end

			-- create the source links.
			insert into tblAltOrderID (OrderID, IDSourceCode, OwnerID, IDValue)
			values (@OrderID, @orderCode, @OwnerID, @orderNumber)
			insert into tblAltOrderID (OrderID, IDSourceCode, OwnerID, IDValue)
			select @OrderID, @alt_ordCode, @OwnerID, alt_order
			from ##cms T1 
				left outer join tblAltOrderID T2 with ( index ( [IDX_AltOrderIDByValue] ) ) on T2.OwnerID = @OwnerID
					and T2.IDSourceCode = @alt_ordCode
					and cast(T1.alt_order as varchar(20)) = T2.IDValue
			where T1.[order] = @orderNumber
				and T2.AltOrderID is null
		end

		fetch next from cCms into @orderNumber, @shipNum
	end
	close cCms
	deallocate cCms

	-- Update the SequenceOrder record to reflect the current state of this subscription
	update T1 set SequenceStatusCode = 'X'
	from tblSequenceOrder T1
		join tblSequence T2 on T1.SequenceID = T2.SequenceID
	where T2.OwnerID = @OwnerID
	update T1 set SequenceStatusCode = 'C'
	from tblSequenceOrder T1
	where exists ( select 1 from ##clubsubs where SequenceOrderID = T1.SequenceOrderID
		and [status] = 'A'
		and (num_ship >= num_sent and num_ship > 0))
	update T1 set SequenceStatusCode = 'A'
	from tblSequenceOrder T1
	where exists ( select 1 from ##clubsubs where SequenceOrderID = T1.SequenceOrderID
		and [status] = 'A'
		and (num_ship < num_sent or num_ship = 0))

	-- Update the current status of the SequenceStep records based on
	-- the ##clubsubs
	update T1 set CurrentCount = T2.CurrentCount,
					NextTriggerDate = T2.NextTriggerDate
	from tblSequenceOrderStep T1
		join ( select T2.SequenceOrderStepID, max( T1.num_sent + T3.Step ) as CurrentCount,
				max( case when T1.[status] = 'A' and (T1.num_ship < T1.num_sent or T1.num_ship = 0) then T1.next_ship else null end ) as NextTriggerDate
			from ##clubsubs T1
				join tblSequenceOrderStep T2 on T1.SequenceOrderID = T2.SequenceOrderID
				join ##tmpSequenceStep T3 on T2.SequenceStepID = T3.SequenceStepID
					and T1.club_code = T3.ClubCode
			group by T2.SequenceOrderStepID ) as T2 on T1.SequenceOrderStepID = T2.SequenceOrderStepID

	-- Add in the OrderTax records
	insert into tblOrderTax ( OrderID, TaxRateID, Amount )
	select T2.OrderID, T3.TaxRateID, T1.Tax
	from openquery( MOMPort, 'select * from invtax where taxtype = ''S'' and tax > 0') as T1
		join tblAltOrderID T2 on cast(T1.[order] as varchar(20)) = T2.IDValue and T2.IDSourceCode = 'E' and T2.OwnerID = @OwnerID
		join tblTaxRate T3 on rtrim(T1.staxcode) + ' Default' = T3.Name
	insert into tblOrderTax ( OrderID, TaxRateID, Amount )
	select T2.OrderID, T3.TaxRateID, T1.Tax
	from openquery( MOMPort, 'select T1.Order, T1.staxcode, T2.Name, T2.State, T1.Tax 
		from invtax T1
			join County T2 on T1.ctaxcode = T2.county
		where T1.taxtype = ''C'' and tax > 0') as T1
		join tblAltOrderID T2 on cast(T1.[order] as varchar(20)) = T2.IDValue and T2.IDSourceCode = 'E' and T2.OwnerID = @OwnerID
		join tblTaxRate T3 on rtrim(T1.name) + ', ' + rtrim(T1.State) + ' Default' = T3.Name
	insert into tblOrderTax ( OrderID, TaxRateID, Amount )
	select T2.OrderID, T3.TaxRateID, T1.Tax
	from openquery( MOMPort, 'select T1.Order, T1.itaxcode, T2.City, T2.State, T1.Tax 
		from invtax T1
			join zip T2 on T1.ctaxcode = T2.zipcode
		where T1.taxtype = ''I'' and tax > 0') as T1
		join tblAltOrderID T2 on cast(T1.[order] as varchar(20)) = T2.IDValue and T2.IDSourceCode = 'E' and T2.OwnerID = @OwnerID
		join tblTaxRate T3 on rtrim(T1.city) + ', ' + rtrim(T1.State) + ' Default' = T3.Name

end
